﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-04-18
 功能说明：企划金额对比
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using com.nm.library;

namespace PlanAPI
{
    [Description("企划金额对比")]
    public class PriceComparisonHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();


        #region 获取[企划金额对比左边的店铺列表]列表 GetShopList
        [MustLogin]
        [Description("获取[企划金额对比左侧店铺]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PriceComparisonModel.Shop.ShopListModel>>))]
        public ApiResult GetShopList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int PriceOrColor = (int)PriceOrColorEnum.价格带;

            string sql = $@"
select p.ShopID,shop.ShopName,p.ParentAreaID,p.QiHuaPrice,p.KindID,
(ba.BudgetAmount * skr.CkPtStandAmountRate) as YuSuanPrice,
(p.QiHuaPrice - ba.BudgetAmount * skr.CkPtStandAmountRate ) as DiffValue 
from (
    select  pc.ExtrSource,pc.CompanyID,pc.Yearv,pc.Monthv,pc.ShopID,pc.KindID, pc.ParentAreaID ,sum(pc.DeepQty * pc.PriceColorSKCQty * pp.PriceValue) as QiHuaPrice
    from ngoods.ngoods_PL_Item_SkcForPCDetail pc 
    left join ngoods.ngoods_PL_Bas_PricePeriod pp on pp.PriceName = pc.PriceColorName 
and  pc.ExtrSource = pp.ExtrSource and pc.CompanyID=pp.CompanyID 
    where 
 pc.ExtrSource = '{UserInfo.UserExtrSource}' and pc.CompanyID='{UserInfo.UserCompanyId}' and
pc.Yearv = {plan.Yearv} AND pc.Monthv = {plan.Monthv} AND pc.PriceOrColor = {PriceOrColor} 
          and ParentAreaID = '{ParentAreaID}' and KindID = '000001'
    group by pc.ExtrSource,pc.CompanyID,pc.Yearv ,pc.Monthv ,pc.ParentAreaID,pc.ShopID,pc.KindID
) p
left join ndw.ndw_shop as shop on p.shopID = shop.shopID and  p.ExtrSource = shop.ExtrSource and p.CompanyID=shop.CompanyID 
left join ngoods.ngoods_PL_Bas_BudgetAmount ba on p.ShopID = ba.ShopID and p.Yearv = ba.Yearv  and p.Monthv = ba.Monthv
 and  p.ExtrSource = ba.ExtrSource and p.CompanyID=ba.CompanyID 
left join ngoods.ngoods_PL_Bas_SeasonMonth season on p.Monthv = season.Monthv and p.ParentAreaID = season.ParentAreaID
 and  p.ExtrSource = season.ExtrSource and p.CompanyID=season.CompanyID 
left join ngoods.ngoods_PL_ShopKindRate skr on p.ShopID = skr.ShopID and p.Yearv = skr.Yearv and season.SeasonID = skr.SeasonID  and p.KindID = skr.KindID
 and  p.ExtrSource = skr.ExtrSource and p.CompanyID=skr.CompanyID 
order by ShopName desc
";

            var dt = Helper.ExecuteDataTable(new VerticaCommand(sql, null));
            var list = ModelConvertHelper<PriceComparisonModel.Shop.ShopListModel>.ConvertToModel(dt);

            foreach (var model in list)
            {
                model.QiHuaPrice = Math.Round(model.QiHuaPrice, 2);
                model.YuSuanPrice = Math.Round(model.YuSuanPrice, 2);
                model.DiffValue = Math.Round(model.DiffValue, 2);
            }

            var result = PaperData<PriceComparisonModel.Shop.ShopListModel>.Create(1000);
            result.Items = list;
            return Success(result);
        }
        #endregion

        #region 获取[企划金额对比]列表 GetDetailList
        [MustLogin]
        [Description("获取[企划金额对比右边详细数据]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PriceComparisonModel.Detail.ListModel>>))]
        public ApiResult GetDetailList(
          [ParamDescription("类别")] string KindID = "",
          [ParamDescription("系列")] string SeriesID = "",
          [ParamDescription("商品层")] string MaterialProperty = "",
          [ParamDescription("品类")] string ModelID = "",
          [ParamDescription("店铺ID")] string ShopID = "",
          [ParamDescription("共性款/差异款")] int DataFlag = -1,//DataFlag==0?"共性款":"差异款" 
          [ParamDescription("页码")] int Page = 1,
          [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int PriceOrColor = (int)PriceOrColorEnum.价格带;

            var listSql = @"SELECT {0} FROM 
(
select 	p.*,
kind.KindName,series.SeriesName,model.ModelName,item.ItemName,shop.ShopName,pp.PriceValue,
ROW_NUMBER()OVER(ORDER BY p.Yearv,p.Monthv) AS rowIndex
FROM ngoods.ngoods_PL_Item_SkcForPCDetail as p
    left join ngoods.ngoods_PL_Bas_PricePeriod pp on p.PriceColorName=pp.PriceName
and  p.ExtrSource = pp.ExtrSource and p.CompanyID=pp.CompanyID 
    left join ndw.ndw_shop as shop on p.shopID = shop.shopID
and  p.ExtrSource = shop.ExtrSource and p.CompanyID=shop.CompanyID 
    left join nods.nods_erp_sd_mat_kind as kind on p.KindID = kind.KindID
and  p.ExtrSource = kind.ExtrSource
    left join nods.nods_erp_sd_mat_series as series on p.SeriesID = series.SeriesID
and  p.ExtrSource = series.ExtrSource
    left join nods.nods_erp_sd_mat_model as model on p.ModelID = model.ModelID
and  p.ExtrSource = model.ExtrSource
    left join nods.nods_erp_SD_Mat_Item as item on p.ItemID = item.ItemID
and  p.ExtrSource = item.ExtrSource
{1}
) table1   ";

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string whereStr = $"where  p.ExtrSource = '{UserInfo.UserExtrSource}' and p.CompanyID='{UserInfo.UserCompanyId}' and  p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入MonthOrWeek跟AreaOrShop条件
            whereStr += $" and p.PriceOrColor = {PriceOrColor}  ";
            //加入前台传来的参数
            whereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            whereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            whereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";
            whereStr += string.IsNullOrWhiteSpace(ModelID) ? "" : $" and p.ModelID = '{ModelID}'";
            if (DataFlag != -1)
            {
                whereStr +=  $" and p.DataFlag = {DataFlag} ";
            }

            //加入查询到店粒度专用参数
            whereStr += string.IsNullOrWhiteSpace(ShopID) ? "" : $" and p.ShopID = '{ShopID}' ";
            #endregion
    
            //获取条数
            //string countSql = string.Format(listSql, "count(1)", whereStr);
            //var count = Convert.ToInt32(Helper.ExecuteScalar(new VerticaCommand(countSql, null)));
            var result = PaperData<PriceComparisonModel.Detail.ListModelInfo>.Create(0, Size, Page);

            // whereSql += " and pp.PriceValue > 0 ";

            listSql = string.Format(listSql, "*", whereStr);
            var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
            var list = ModelConvertHelper<PriceComparisonModel.Detail.ListModel>.ConvertToModel(dt);

            var resultList = new List<PriceComparisonModel.Detail.ListModelInfo>();
            //二次分组
            var tmpList = list.GroupBy(x => new { x.Yearv, x.Monthv, x.PriceOrColor, x.KindID, x.SeriesID, x.MaterialProperty, x.ModelID, x.ItemID, x.DataFlag, x.ShopID }, (model, modelGroup) => new
            {
                Item = model,
                DeepQty = modelGroup.FirstOrDefault().DeepQty,
                Datas = modelGroup.GroupBy(y => new { y.Weekv }, (item, itemGroup) => new
                {
                    Item = item,
                    ItemGroup = itemGroup.OrderBy(x=>x.PriceValue)
                })
            });

            #region 排序

            tmpList = tmpList.ToList().OrderBy(x => x.Item.Yearv)
          .ThenBy(x => x.Item.Monthv)
          .ThenBy(x => x.Item.KindID)
          .ThenBy(x => x.Item.SeriesID)
          .ThenBy(x => x.Item.MaterialProperty)
          .ThenBy(x => x.Item.ModelID)
          .ThenBy(x => x.Item.ItemID);

            #endregion

            decimal totalSum = 0;
            tmpList.ToList().ForEach(item1 =>
            {
                var tmpModel = new PriceComparisonModel.Detail.ListModelInfo
                {
                    Yearv = item1.Item.Yearv,
                    Monthv = item1.Item.Monthv,
                    KindID = item1.Item.KindID,
                    SeriesID = item1.Item.SeriesID,
                    MaterialProperty = item1.Item.MaterialProperty,
                    ModelID = item1.Item.ModelID,
                    ItemID = item1.Item.ItemID,
                    DeepQty = item1.DeepQty,
                    ShopID = item1.Item.ShopID,
                    DataFlag = item1.Item.DataFlag
                };
                //企划金额＝sum(小品类月企划金额＝周汇总(DeepQty * PriceColorSKCQty * PriceValue))
                decimal priceMonthSum = 0;
                item1.Datas.OrderBy(y => y.Item.Weekv).ToList().ForEach(week1 =>
                {
                    var weekGroup = new PriceComparisonModel.Detail.WeekGroup(week1.Item.Weekv);

                    weekGroup.PriceWeekSum = week1.ItemGroup.Sum(x => x.DeepQty * x.PriceColorSKCQty * x.PriceValue);
                    priceMonthSum += weekGroup.PriceWeekSum;

                    week1.ItemGroup.ToList().ForEach(price1 => {
                        weekGroup.WeekList.Add(price1);
                    });
                    tmpModel.List.Add(weekGroup);
                });

                tmpModel.PriceMonthSum = priceMonthSum;

                totalSum += priceMonthSum;
                resultList.Add(tmpModel);
            });


            result.Items = resultList.ToList();
            return Success(new { TotalPrice = totalSum, PageData = result });
        }
        #endregion




        #region 修改[企划金额对比小品类深度] UpdateDeepQty
        [JsonType]
        [MustLogin]
        [Description("修改[企划金额对比小品类深度]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateDeepQty(PriceComparisonModel.Detail.UpdateModelList list)
        {
            #region 验证是否在企划期，不是就直接返回
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }
            #endregion

            int PriceOrColor = (int)PriceOrColorEnum.价格带;
            var cmdList = new List<CommandInfo>();
            var shopId = "";

            try
            {
                foreach (var model in list.List)
                {
                    shopId = model.ShopID;

                    string sql = $@"UPDATE ngoods.ngoods_PL_Item_SkcForPCDetail 
SET DeepQty = {model.DeepQty}, CheckState = 1 
WHERE  
 ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and
PriceOrColor = {PriceOrColor} and 
Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and 
ShopID = '{model.ShopID}' and 
KindID = '{model.KindID}' and SeriesID = '{model.SeriesID}' and  MaterialProperty = '{model.MaterialProperty}' and 
ModelID = '{model.ModelID}' and  ItemID = '{model.ItemID}' and DataFlag = {model.DataFlag} 
";
                    cmdList.Add(new CommandInfo(sql, null));
                }

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }


                var tmpModel = new PriceComparisonModel.Shop.ShopListUpdateModel();

                var modelSql = $@"select pc.ShopID ,sum(pc.DeepQty * pc.PriceColorSKCQty * per.PriceValue) as QiHuaPrice
from ngoods.ngoods_PL_Item_SkcForPCDetail pc
left join ngoods.ngoods_PL_Bas_PricePeriod per on per.PriceName = pc.PriceColorName 
and  pc.ExtrSource = per.ExtrSource and pc.CompanyID=per.CompanyID 
where 
 pc.ExtrSource = '{UserInfo.UserExtrSource}' and pc.CompanyID='{UserInfo.UserCompanyId}' and
pc.Yearv = {plan.Yearv} AND pc.Monthv = {plan.Monthv} AND pc.PriceOrColor = {PriceOrColor}  and ShopID = '{shopId}'
group by pc.Yearv ,pc.Monthv ,pc.ParentAreaID,ShopID";

                var dt = Helper.ExecuteDataTable(new VerticaCommand(modelSql, null));
                var tmpList = ModelConvertHelper<PriceComparisonModel.Shop.ShopListUpdateModel>.ConvertToModel(dt);
                if (tmpList != null && tmpList.Count > 0)
                {
                    tmpModel = tmpList[0];
                }

                return Success(tmpModel);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 修改[企划金额对比SKC数量] UpdatePriceColorSKCQty
        [JsonType]
        [MustLogin]
        [Description("修改[企划金额对比SKC数量]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdatePriceColorSKCQty(PriceComparisonModel.Detail.UpdateModelList list)
        {
            #region 验证是否在企划期，不是就直接返回
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }
            #endregion

            int PriceOrColor = (int)PriceOrColorEnum.价格带;
            var cmdList = new List<CommandInfo>();
            var shopId = "";
            var parentAreaId = "";
            try
            {
                foreach (var model in list.List)
                {
                    shopId = model.ShopID;
                    parentAreaId = model.ParentAreaID;

                    string sql = $@"UPDATE ngoods.ngoods_PL_Item_SkcForPCDetail 
SET PriceColorSKCQty = {model.PriceColorSKCQty},CheckState = 1
WHERE  
 ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and
PriceOrColor = {PriceOrColor} and 
Yearv = {plan.Yearv} and Monthv = {plan.Monthv} and Weekv = {model.Weekv} and 
ShopID = '{model.ShopID}' and 
KindID = '{model.KindID}' and SeriesID = '{model.SeriesID}' and  MaterialProperty = '{model.MaterialProperty}' and 
ModelID = '{model.ModelID}' and  ItemID = '{model.ItemID}' and DataFlag = {model.DataFlag} and 
PriceColorName = '{model.PriceColorName}'
";
                    cmdList.Add(new CommandInfo(sql, null));
                }

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }

                var tmpModel = new PriceComparisonModel.Shop.ShopListUpdateModel();

                var modelSql = $@"select pc.ShopID ,sum(pc.DeepQty * pc.PriceColorSKCQty * per.PriceValue) as QiHuaPrice
from ngoods.ngoods_PL_Item_SkcForPCDetail pc
left join ngoods.ngoods_PL_Bas_PricePeriod per on per.PriceName = pc.PriceColorName
and  pc.ExtrSource = per.ExtrSource and pc.CompanyID=per.CompanyID 
where 
 pc.ExtrSource = '{UserInfo.UserExtrSource}' and pc.CompanyID='{UserInfo.UserCompanyId}' and
pc.Yearv = {plan.Yearv} AND pc.Monthv = {plan.Monthv} AND pc.PriceOrColor = {PriceOrColor}  and ShopID = '{shopId}'
group by pc.Yearv ,pc.Monthv ,pc.ParentAreaID,ShopID";

                var dt = Helper.ExecuteDataTable(new VerticaCommand(modelSql, null));
                var tmpList = ModelConvertHelper<PriceComparisonModel.Shop.ShopListUpdateModel>.ConvertToModel(dt);
                if (tmpList != null && tmpList.Count > 0)
                {
                    tmpModel = tmpList[0];
                }

                return Success(tmpModel);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region GetPriceChart

        [MustLogin]
        [Description("获取各区域企划金额数量(首页图表专用)")]
        [ReturnType(typeof(IList<PriceComparisonModel.ChartModel>))]
        public ApiResult GetAreasPriceChart()
        {
            var list = new List<PriceComparisonModel.ChartModel>();

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan != null)
            {
                string sql = $@"select 
pc.ParentAreaID ,sum(pc.DeepQty * pc.PriceColorSKCQty * per.PriceValue) as QiHuaPrice
from ngoods.ngoods_PL_Item_SkcForPCDetail pc 
left join ngoods.ngoods_PL_Bas_PricePeriod per on per.PriceName = pc.PriceColorName 
and  pc.ExtrSource = per.ExtrSource and pc.CompanyID=per.CompanyID 
where 
 pc.ExtrSource = '{UserInfo.UserExtrSource}' and pc.CompanyID='{UserInfo.UserCompanyId}' and
pc.Yearv = {plan.Yearv} AND pc.Monthv = {plan.Monthv} AND pc.PriceOrColor = 0 
group by pc.Yearv ,pc.Monthv ,pc.ParentAreaID";

                var dt = Helper.ExecuteDataTable(new VerticaCommand(sql, null));
                list = ModelConvertHelper<PriceComparisonModel.ChartModel>.ConvertToModel(dt).ToList();
            }

            return Success(list);
        }

        #endregion

        






        enum PriceOrColorEnum
        {
            价格带 = 0,
            颜色 = 1
        }
    }
}
